
-- exec SP_UPDATE_RELEASES_WITH_LATEST_SPEC_ID 15, 'TEST'

ALTER PROC sp_update_releases_with_latest_spec_id
@RELEASE_ID INT,
@PROCESS_TYPE VARCHAR(20)
AS
BEGIN
	DECLARE @PROCESS_STEP_ID BIGINT
	DECLARE @SPEC_ID BIGINT

	IF( @PROCESS_TYPE = 'SORT' )
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESSSTEP_ID FROM SORT_RELEASE_TABLE WHERE SORT_REL_ID = @RELEASE_ID
		SELECT @SPEC_ID = PST.SPECFILE_ID FROM PRODUCT_SPEC_TABLE PST, SPEC_TABLE ST WHERE PST.SPECFILE_ID = ST.SPECFILE_ID AND PST.PROCESSSTEP_ID = @PROCESS_STEP_ID
		UPDATE SORT_RELEASE_TABLE SET SPEC_FILE_ID = @SPEC_ID WHERE SORT_REL_ID = @RELEASE_ID
	END
	ELSE IF( @PROCESS_TYPE = 'ASSEMBLY' )
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
		SELECT @SPEC_ID = PST.SPECFILE_ID FROM PRODUCT_SPEC_TABLE PST, SPEC_TABLE ST WHERE PST.SPECFILE_ID = ST.SPECFILE_ID AND PST.PROCESSSTEP_ID = @PROCESS_STEP_ID
		UPDATE ASSM_RELEASE_TABLE SET ASSEMBLY_SPEC_FILE_ID = @SPEC_ID WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
	END
	ELSE IF( @PROCESS_TYPE = 'TEST' )
	BEGIN
		DECLARE @PART_NUMBER VARCHAR(50)
		DECLARE @PROCESS_STEP_ID_FOR_TEST INT
		SELECT @PROCESS_STEP_ID = PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
		SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID
		SELECT @PROCESS_STEP_ID_FOR_TEST = P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'TEST'
		--print convert( varchar, @PROCESS_STEP_ID )
		SELECT @SPEC_ID = PST.SPECFILE_ID FROM PRODUCT_SPEC_TABLE PST, SPEC_TABLE ST WHERE PST.SPECFILE_ID = ST.SPECFILE_ID AND PST.PROCESSSTEP_ID = @PROCESS_STEP_ID_FOR_TEST
		--print convert( varchar, @SPEC_ID )
		UPDATE ASSM_RELEASE_TABLE SET TEST_SPEC_FILE_ID = @SPEC_ID WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
	END
END


